﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MODEL;
using System.Data;
using System.Data.OleDb;
namespace DAL
{
    public class DalTuiKu
    {
        public List<TuiKu> GetListByPy(string py)
        {
            string sql = "select id,chukuid,dicid,goodsnumber,createdate,goodspy,tuiremark from tuiku where goodspy like @goodspy";
            DataTable dt = AccessHelp.ExecuteReader(sql,new OleDbParameter("@goodspy","%"+py+"%"));
            return TableToList(dt);
        }
        public DataTable GetListBy(string py)
        {
            string sql = "select t.id,d.goodsname,d.goodsmodel,d.goodsspe,d.goodsunit,t.chukuid,t.goodsnumber,t.createdate,t.goodspy,t.tuiremark from tuiku t inner join dic d on t.dicid = d.id where t.goodspy like @goodspy";
            DataTable dt = AccessHelp.ExecuteReader(sql, new OleDbParameter("@goodspy", "%" + py + "%"));
            //return TableToList(dt);
            return dt;
        }
        List<TuiKu> TableToList(DataTable dt)
        {
            List<TuiKu> tuikus = new List<TuiKu>();
            foreach (DataRow row in dt.Rows)
            {
                TuiKu t = new TuiKu();
                t.Id = (int)row[0];
                t.ChuKuId = (int)row[1];
                t.DicId = (int)row[2];
                t.GoodsNumber = (int)row[3];
                t.CreateDate = DateTime.Parse(row[4].ToString());
                t.GoodPy = row[5].ToString();
                t.TuiRemark = row[6].ToString();
                tuikus.Add(t);
            }
            return tuikus;
        }

        public DataTable GetList(string py, DateTime startDate, DateTime endDate)
        {
            string sql = "select t.id,d.goodsname,d.goodsmodel,d.goodsspe,d.goodsunit,t.chukuid,t.goodsnumber,t.createdate,d.goodspy,t.tuiremark from tuiku t inner join dic d on t.dicid = d.id where d.goodspy like @goodspy and (t.createdate >= @startDate and t.createdate <= @endDate) order by t.createdate ";
            DateTime sDate = startDate;
            DateTime eDate = endDate.AddDays(1).AddMilliseconds(-1000);
            OleDbParameter[] pms = new OleDbParameter[] 
            {
                new OleDbParameter("@goodspy", "%" + py + "%"),
                new OleDbParameter("@startDate",sDate.ToShortDateString()),
                new OleDbParameter("@endDate",eDate.ToShortDateString())
            };
            DataTable dt = AccessHelp.ExecuteReader(sql,pms);
            //return TableToList(dt);
            return dt;
        }

        public List<TuiKu> GetObjectByDicId(int dicid, DateTime date)
        {
            string sql = "select id,chukuid,dicid,goodsnumber,createdate,goodspy,tuiremark from tuiku where dicid=@dicid";
            string startDate = date.Date.ToString();
            string endDate = date.Date.AddMonths(1).AddSeconds(-1).ToString();
            OleDbParameter[] pms = new OleDbParameter[] 
            {                
                new OleDbParameter("@id",dicid),
                new OleDbParameter("@startDate",startDate),
                new OleDbParameter("@endDate",endDate)
            };
            DataTable dt = AccessHelp.ExecuteReader(sql,pms);
            return TableToList(dt);
        }
    }
}
